﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Data.SQLite;
using System.Data;

namespace SA2012TBRace
{
	static class SQLiteDatabase
	{
		private static string _ConnString;

		public static IEnumerable<Trackable> GetAllTBs(string database)
		{
			_ConnString = "data source=\"" + database + "\"";
			const string sqlStatement = "Select Code, Name, PlacedBy, Country, State, County, Latitude, Longitude, UserData, User3, Symbol from Caches";
			var trackables = new List<Trackable>();

			using (var conn = new SQLiteConnection(_ConnString))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				SQLiteDataReader reader = null;

				conn.Open();

				try
				{
					reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						trackables.Add(
							new Trackable
								{
									Code = reader["Code"].ToString(),
									Name = reader["Name"].ToString(),
									Owner = reader["PlacedBy"].ToString(),
									Country = reader["Country"].ToString(),
									State = reader["State"].ToString(),
									County = reader["County"].ToString(),
									RaceType = reader["Symbol"].ToString(),
									Latitude = Double.Parse(reader["Latitude"].ToString()),
									Longitude = Double.Parse(reader["Longitude"].ToString()),
									TrackableType = reader["UserData"].ToString(),
									TrackableParentName = reader["User3"].ToString()
								}
							);
					}
				}
				finally
				{
					if (reader != null) reader.Close();
				}
			}

			return trackables.AsEnumerable();
		}

		public static IEnumerable<ItemLog> GetAllLogs(string code, string database)
		{
			_ConnString = "data source=\"" + database + "\"";

			const string sqlStatement = "Select * from Logs where lParent=@code order by lDate ASC, lLogId ASC;";

			var logs = new List<ItemLog>();

			using (var conn = new SQLiteConnection(_ConnString))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				cmd.Parameters.Add(new SQLiteParameter("@code", code));

				SQLiteDataReader reader = null;

				conn.Open();

				try
				{
					reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						var lBy = reader["lBy"].ToString().Split(new[] {"$~"},2,StringSplitOptions.None);

						if (lBy.Length == 1)
						{
							Array.Resize(ref lBy, 2);
							lBy[1] = lBy[0];
							lBy[0] = "<no cache>";
						}

						logs.Add(
							new ItemLog
								{
									LogId = int.Parse(reader["lLogId"].ToString()),
									LogType = reader["lType"].ToString(),
									Location = lBy[0],
									ByCacher = lBy[1],
									Date = (DateTime)reader["lDate"],
									Latitude = String.IsNullOrEmpty(reader["lLat"].ToString()) ? 0 : Double.Parse(reader["lLat"].ToString()),
									Longitude = String.IsNullOrEmpty(reader["lLon"].ToString()) ? 0 : Double.Parse(reader["lLon"].ToString()),
								}
							);
					}
				}
				finally
				{
					if (reader != null) reader.Close();
				}
			}

			return logs.AsEnumerable();
		}

		internal static CacheInfo GetCacheDetails(string gcCode, string cacheDatabaseLocation)
		{
			_ConnString = "data source=\"" + cacheDatabaseLocation + "\"";

			const string sqlStatement = "Select Code, Name, CacheType, Country, State, County, Elevation, Longitude, Latitude, Container from Caches where Code=@gcCode;";

			CacheInfo cacheInfo = null;

			using (var conn = new SQLiteConnection(_ConnString))
			{
				var cmd = new SQLiteCommand(sqlStatement, conn);

				cmd.Parameters.Add(new SQLiteParameter("@gcCode", gcCode));

				SQLiteDataReader reader = null;

				conn.Open();

				try
				{
					reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

					while (reader.Read())
					{
						cacheInfo = new CacheInfo
							{
								Code = reader["Code"].ToString(),
								Name = reader["Name"].ToString(),
								CacheType = reader["CacheType"].ToString(),
								Country = reader["Country"].ToString(),
								State = reader["State"].ToString(),
								County = reader["County"].ToString(),
								Elevation = Double.Parse(reader["Elevation"].ToString()),
								Latitude = Double.Parse(reader["Latitude"].ToString()),
								Longitude = Double.Parse(reader["Longitude"].ToString()),
								Container = reader["Container"].ToString()
							};
					}
				}
				finally
				{
					if (reader != null) reader.Close();
				}
			}

			return cacheInfo;
		}
	}
}
